using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
//using System.Data.DataSetExtensions;
using System.Linq;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.XPath;
using PS.WebParts.Rollup.Query;
using PS.WebParts.Rollup.Utils;
using PS.WebParts.Rollup.DAL.OM;
using PS.WebParts.Rollup.DAL.OM.Render;
using System.Data.SqlTypes;
using System.IO.Compression;
using System.IO;

namespace PS.WebParts.Rollup.Query
{
    public abstract class DAL : IDisposable
    {
        #region Private
        private SqlConnection connection;

        //tp_ServerTemplate = @ServerTemplate
        private PSSiteDataRollupOptions options;
        private uint startRow;
        private uint rowLimit;
        private PSSiteDataSecuritySettings security;
        private string query;
        private bool isAdmin;
        private Dictionary<string, string> allFields;
        private Dictionary<string, string> userFields;
        private Dictionary<string, LookupField> lookupFields;
        private Dictionary<string, LookupField> lookupMultiFields;
        private Dictionary<string, string> unfoldedLookupFields;
        private IDictionary<string, FieldItem> builtinFields = new Dictionary<string, FieldItem>();
        private readonly Hashtable lookupFieldsIndex = new Hashtable();
        private Dictionary<string, string> matches;

        private bool webAppLevel;
        private FromTerm userDataTable = FromTerm.Table("UserData");
        private FromTerm docsTable = FromTerm.Table("Docs");
        private static FromTerm allListsTable = FromTerm.Table("AllLists");
        private FromTerm websTable = FromTerm.Table("Webs");
        private FromTerm userInfoTable = FromTerm.Table("UserInfo");
        private FromTerm udjTable = FromTerm.Table("UserDataJunctions");
        private FromTerm allUserDataTable = FromTerm.Table("AllUserData");
        private FromTerm roleAssignmentTable = FromTerm.Table("RoleAssignment");
        private FromTerm groupMembershipTable = FromTerm.Table("GroupMembership");
        //TODO: Add "Sites" table to temporary views
        private FromTerm sitesTable = FromTerm.Table("Sites");

        private string tempTable = "##Temp";

        private readonly XmlNamespaceManager SPNamespaceManager = new XmlNamespaceManager(new NameTable());

        private IdentityImpersonator imp = new IdentityImpersonator();

        private readonly string docsScopeIdQuery = @"
        SELECT   DISTINCT  ScopeId
        FROM       {0}
        WHERE     PrincipalId IN 
        (
            SELECT     GroupId
            FROM       {1}
            WHERE     (MemberId = @UserID) OR MemberId IN  
                (SELECT tp_ID
                FROM {2}
                WHERE tp_Login in ({3})
                )


        ) OR PrincipalId = @UserID
            OR PrincipalId IN  
            (SELECT tp_ID
            FROM {2}
            WHERE tp_Login in ({3})
            )";

        private readonly string docsScopeIdQueryFBA = @"
        SELECT   DISTINCT  ScopeId
        FROM       {0}
        WHERE     PrincipalId = @UserID 
        OR PrincipalId IN 
        (
            SELECT     GroupId
            FROM       {1}
            WHERE     MemberId = @UserID
        )";

        private ICacheProvider cache;
        private bool isFBAUser;
        private readonly int executeAttemtCount = 20;
        private readonly int sqlTimeout = 500;
        private int userID;
        #endregion

        public string ContentTypeV14Filter = string.Empty;

        public static FromTerm AllListsTable
        {
            get { return allListsTable; }
        }

        public void SetWebAppLevel()
        {
            webAppLevel = true;
            userDataTable = FromTerm.Table("tempdb.dbo.UserDataView", "UserData");
            allUserDataTable = FromTerm.Table("tempdb.dbo.AllUserDataView");
            docsTable = FromTerm.Table("tempdb.dbo.DocsView");
            allListsTable = FromTerm.Table("tempdb.dbo.AllListsView");
            websTable = FromTerm.Table("tempdb.dbo.WebsView");
            userInfoTable = FromTerm.Table("tempdb.dbo.UserInfoView");
            udjTable = FromTerm.Table("tempdb.dbo.UserDataJunctionsView");
        }

        internal SelectQuery GetLists(bool isAdmin, bool isFBAUser)
        {
            //FromTerm ft = FromTerm.Table("Lists");
            FromTerm ft = AllListsTable;
            SelectQuery sq = new SelectQuery();
            sq.FromClause.BaseTable = ft;

            sq.Columns.Add(new SelectColumn("tp_ID", ft));
            //sq.Columns.Add(new SelectColumn("tp_Title", ft));
            //sq.Columns.Add(new SelectColumn("tp_Fields", ft));
            //sq.Columns.Add(new SelectColumn("tp_ServerTemplate", ft));


            if (!isAdmin)
            {
                string scopeId = isFBAUser
                                     ? string.Format(docsScopeIdQueryFBA,
                                                     roleAssignmentTable.RefName, groupMembershipTable.RefName)
                                     :
                                         string.Format(docsScopeIdQuery, roleAssignmentTable.RefName,
                                                       groupMembershipTable.RefName,
                                                       userInfoTable.RefName,
                                                       CurrentUser.GetDomainGroupsCSV());
                ;

                sq.WherePhrase.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("tp_ScopeId", ft), scopeId));
            }

            return sq;
        }

        internal WhereTerm CreateSiteScope(Guid siteID)
        {
            //SelectQuery sq = GetLists();
            string siteFilter =
                string.Format(@"SELECT Id
		FROM {0}
		WHERE SiteId = '{1}'", websTable.RefName, siteID.ToString("D"));

            WhereTerm wt = WhereTerm.CreateIn(SqlExpression.Field("tp_WebId", AllListsTable), siteFilter);

            return wt;
        }

        internal WhereTerm CreateWebScope(Guid webID)
        {
            WhereTerm wt = WhereTerm.CreateCompare(SqlExpression.Field("tp_WebId", AllListsTable),
                                                             SqlExpression.Constant(
                                                                 SqlConstant.String(webID.ToString("D"))),
                                                             CompareOperator.Equal);
            return wt;
        }

        internal WhereTerm CreateRecursiveScope(string webUrl)
        {
            if (webUrl.StartsWith("/"))
            {
                webUrl = webUrl.Substring(1);
            }

            //TODO: check FullUrl table name - it should be set explicitly.
            string recursiveUrlPattern = string.IsNullOrEmpty(webUrl) ? "" : webUrl + '/';
            string webFilter = string.Format(@"SELECT Id
		FROM {0}
		WHERE (FullUrl LIKE '" + recursiveUrlPattern + "' + '%') OR (FullUrl LIKE '{1}')", websTable.RefName, webUrl);
            WhereTerm wt = WhereTerm.CreateIn(SqlExpression.Field("tp_WebId", AllListsTable), webFilter);
            return wt;
        }

        internal SqlUnion GetQuery(ref Dictionary<string, string> fields)
        {
            SqlUnion res = new SqlUnion();

            foreach (Guid userInfoListId in QueryParams.Instance.ListUserInfos.Keys)
            {
                SelectQuery sq = new SelectQuery();

                sq.FromClause.BaseTable = userDataTable;

                AddColumns(fields, sq);

#if !v14
                JoinCondition[] joins = new JoinCondition[]
                                            {
                                                new JoinCondition("tp_SiteId", "SiteId"),
                                                new JoinCondition("tp_DirName", "DirName"),
                                                new JoinCondition("tp_LeafName", "LeafName"),
                                                new JoinCondition("tp_Level", "Level")
                                            };                

#else
                JoinCondition[] joins = new JoinCondition[]
                                            {
                                                new JoinCondition("tp_SiteId", "SiteId"),
                                                new JoinCondition("tp_DocId", "Id"),
                                                //new JoinCondition("tp_DirName", "DirName"),
                                                //new JoinCondition("tp_LeafName", "LeafName"),
                                                new JoinCondition("tp_Level", "Level")
                                            };

#endif

                sq.FromClause.Join(JoinType.Inner, userDataTable, docsTable, joins);

                sq.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("tp_RowOrdinal", userDataTable),
                                                                 SqlExpression.Constant(SqlConstant.Number(0)),
                                                                 CompareOperator.Equal));

                // MJM Only Current Version
                sq.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("IsCurrentVersion", docsTable),
                                                                 SqlExpression.Constant(SqlConstant.Number(1)),
                                                                 CompareOperator.Equal));

#if v14
                // Temp --------------
                if (!String.IsNullOrEmpty(this.ContentTypeV14Filter))
                    sq.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Raw("CAST(UserData.tp_ContentTypeId as nvarchar)"),
                                                                     SqlExpression.Raw(
                                                                        string.Format("( select top 1 Cast(ContentTypeId as nvarchar) + '%' from ContentTypes where ResourceDir = '{0}' order by ContentTypeId desc )",
                                                                                    this.ContentTypeV14Filter)
                                                                    ),
                                                                 CompareOperator.Like));
                //-------------------------

                //str = string.Empty;// string.Format("<Where><Like><FieldRef Name='ContentType' /><Value Type='Text'>( select top 1 Cast(ContentTypeId as nvarchar) + '%' from ContentTypes where ResourceDir = '{0}' order by ContentTypeId desc )</Value></Like></Where>", contentType);
#endif

                if (security.SecurityLevel == SecurityLevel.Item && !isAdmin)
                {
                    string scopeId = isFBAUser
                                         ? string.Format(docsScopeIdQueryFBA,
                                                         roleAssignmentTable.RefName, groupMembershipTable.RefName)
                                         :
                                             string.Format(docsScopeIdQuery, roleAssignmentTable.RefName,
                                                           userInfoTable.RefName,
                                                           groupMembershipTable.RefName,
                                                           CurrentUser.GetDomainGroupsCSV());
                    sq.WherePhrase.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("ScopeId", docsTable), scopeId));
                }

                if (options.IncludeListData != 0)
                {
                    AddIncludeDataJoins(ref sq);
                }

                if ((options.IncludeListData & IncludeMetaDataOptions.List) != 0)
                {
                    IncludeListDataList(ref sq, ref fields);
                }

                if ((options.IncludeListData & IncludeMetaDataOptions.Web) != 0)
                {
                    IncludeListDataWeb(ref sq, ref fields);
                }

                if ((options.IncludeListData & IncludeMetaDataOptions.IDs) != 0)
                {
                    IncludeListDataIDs(ref sq, ref fields);
                }

                sq.WherePhrase.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("tp_ListId",
                                                                                userDataTable),
                                                            string.Format(
                                                                "SELECT listId FROM {0} WHERE user_list_id='{1}'",
                                                                tempTable, userInfoListId)));
                //SqlConstantCollection.FromList(listIDs)));

                Dictionary<string, string> fieldsCopy = CloneDictonary(fields);
                Dictionary<string, string> userFieldsCopy = CloneDictonary(userFields);

                AddColumns(userFieldsCopy, sq);

                QueryLookupFiledsSchema();

                AddLookupFields(sq);

                sq = AddLookupMultiFields(sq, userInfoListId);

                AddUserTypeFields(ref sq, fieldsCopy, userFieldsCopy);

                if (!string.IsNullOrEmpty(query))
                {
                    CAMLParser cp = new CAMLParser(fields, allFields, lookupFields, userFields, userID);

                    cp.Parse(query);

                    OrderByTerm[] orderByTerms = cp.GetOrderByTerms();

                    SelectQuery outerQuery = new SelectQuery();

                    //foreach (SelectColumn column in sq.Columns)
                    //{
                    //    outerQuery.Columns.Add(new SelectColumn(column.RenderName));
                    //}

                    outerQuery.Columns.Add(new SelectColumn("*"));

                    outerQuery.FromClause.BaseTable = FromTerm.SubQuery(sq, sq.FromClause.BaseTable.RefName);

                    outerQuery.OrderByTerms.AddRange(orderByTerms);

                    cp.WhereClauseAddTerms(outerQuery.WherePhrase);

                    sq = outerQuery;
                }

                if (lookupMultiFields.Count > 0)
                {
                    sq.OrderByTerms.Add(new OrderByTerm("tp_Id", OrderByDirection.Ascending));
                }

                res.Add(sq, DistinctModifier.All);
            }

            return res;
        }

        private static void AddColumns(Dictionary<string, string> fields, SelectQuery selectQuery)
        {
            if (selectQuery == null || fields == null)
                return;

            List<string> strFields = new List<string>(fields.Values);

            List<SelectColumn> columns = strFields.ConvertAll(
                strField => new SelectColumn(strField, selectQuery.FromClause.BaseTable)
                );

            selectQuery.Columns.AddRange(columns.ToArray());
        }

        internal void AddIncludeDataJoins(ref SelectQuery sq)
        {
            //JOIN dbo.AllLists as lists ON (data.tp_ListId = lists.tp_ID) 
            sq.FromClause.Join(JoinType.Inner, sq.FromClause.BaseTable, AllListsTable,
                               new JoinCondition("tp_ListId", "tp_ID"));

            //JOIN dbo.Webs as  webs ON (lists.tp_WebId = webs.Id)
            sq.FromClause.Join(JoinType.Inner, AllListsTable, websTable,
                               new JoinCondition("tp_WebId", "Id"));

        }

        internal void IncludeListDataList(ref SelectQuery sq, ref Dictionary<string, string> fields)
        {
            sq.Columns.Add(new SelectColumn("tp_Title", AllListsTable, "_ListTitle"));
            fields.Add("_ListTitle", "_ListTitle");

            sq.Columns.Add(new SelectColumn(SqlExpression.Raw("N'/' + " + sq.FromClause.BaseTable.RefName + ".tp_DirName"), "_ListUrl"));
            fields.Add("_ListUrl", "_ListUrl");
        }

        internal void IncludeListDataIDs(ref SelectQuery sq, ref Dictionary<string, string> fields)
        {
            sq.Columns.Add(new SelectColumn("tp_ID", sq.FromClause.BaseTable, "_ItemID"));
            fields.Add("_ItemID", "_ItemID");

            sq.Columns.Add(new SelectColumn("Id", websTable, "_WebID"));
            fields.Add("_WebID", "_WebID");

            sq.Columns.Add(new SelectColumn("tp_ID", AllListsTable, "_ListID"));
            fields.Add("_ListID", "_ListID");
        }

        internal void IncludeListDataWeb(ref SelectQuery sq, ref Dictionary<string, string> fields)
        {
            sq.Columns.Add(new SelectColumn(SqlExpression.Raw("N'/' + " + websTable.RefName + ".FullUrl"), "_SiteUrl"));
            fields.Add("_SiteUrl", "_SiteUrl");

            sq.Columns.Add(new SelectColumn("Title", websTable, "_SiteTitle"));
            fields.Add("_SiteTitle", "_SiteTitle");
        }

        public void Connect(string connectionString, ICacheProvider cacheProvider)
        {
            imp.UseAppPoolIdentity();

            Regex r = new Regex(@"Timeout=\d+", RegexOptions.Singleline);

            connectionString = r.Replace(connectionString, string.Format("Timeout={0}", sqlTimeout));

            tempTable = "##Temp_" + Guid.NewGuid().ToString().Replace('-', '_');

            connection = new SqlConnection(connectionString);

            connection.Open();

            SPNamespaceManager.AddNamespace("sp", "http://schemas.microsoft.com/sharepoint/");

            this.cache = cacheProvider;

            ReadBuiltinFields();
        }

        public void Dispose()
        {
            imp.ReturnToCurrentUser();
            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
            }
        }

        internal DataTable GetRollupGeneralImpl(int userID, Dictionary<string, string> fields)
        {
            this.userID = userID;
            DataTable result = null;

            if (fields.Count > 0)
            {
                //List<string> listIDs = accessibleLists.ConvertAll<string>(
                //    delegate(DBList list)
                //    {
                //        return list.Id.ToString("D");
                //    }
                //    );

                SqlUnion su = GetQuery(ref fields);

                SqlServerRenderer sqlRenderer = new SqlServerRenderer();

                string getDataWithParams = sqlRenderer.RenderUnion(su);

                SimpleLogger.Write("query = {0}", getDataWithParams);

                SqlCommand extractData = new SqlCommand(getDataWithParams, connection);
                SqlParameter userIdParam = new SqlParameter("UserID", SqlDbType.Int) { Value = userID };

                extractData.Parameters.Add(userIdParam);

                foreach (KeyValuePair<string, string> pair in userFields)
                {
                    fields.Add(pair.Key, pair.Value);
                }

                foreach (KeyValuePair<string, LookupField> pair in lookupFields)
                {
                    fields.Add(pair.Value.Name, pair.Value.ColumnnName);
                }

                if (lookupMultiFields.Count > 0)
                {
                    foreach (KeyValuePair<string, LookupField> pair in lookupMultiFields)
                    {
                        fields.Add(pair.Value.Name, unfoldedLookupFields[pair.Value.SourceListField]);
                    }
                    fields.Add("tp_Id", "tp_Id");
                }

                result = GetResult(extractData, fields, result);

                if (lookupMultiFields.Count > 0)
                {
                    //result = GroupRowsByTp_ID(result);
                    result = GroupRowsByTp_ID_LINQ(result);
                }

                result = ApplyRowLimits(result);

                extractData.Dispose();
            }
            return result;
        }

        private DataTable ApplyRowLimits(DataTable table)
        {
            if (rowLimit > 0)
            {
                DataTable res = table.Copy();
                res.Rows.Clear();

                for (uint i = startRow; i < Math.Min(startRow + rowLimit, table.Rows.Count); i++)
                {
                    res.ImportRow(table.Rows[(int)i]);
                }

                return res;
            }
            return table;
        }

        private DataTable GroupRowsByTp_ID_LINQ(DataTable sourceTable)
        {
            var res = from item in sourceTable.AsEnumerable()
                      group item by int.Parse(item.Field<string>("tp_Id"))
                          into itemGroup
                          select itemGroup.Aggregate((accum, row) =>
                          {
                              var curRow = sourceTable.NewRow();
                              for (int i = 0; i < accum.ItemArray.Length; i++)
                              {
                                  string item1 = accum[i].ToString();
                                  string item2 = row[i].ToString();
                                  if (!item1.Equals(item2))
                                  {
                                      if (!item1.StartsWith(";#"))
                                      {
                                          item1 = ";#" + item1;
                                      }
                                      curRow[i] = item1 + ";#" + item2;
                                  }
                                  else
                                  {
                                      curRow[i] = item1;
                                  }
                              }
                              return curRow;
                          });
            return res.CopyToDataTable();
        }

        [Obsolete]
        private DataTable GroupRowsByTp_ID(DataTable sourceTable)
        {
            if (sourceTable.Rows.Count < 2)
                return sourceTable;

            DataTable res = new DataTable("result");
            foreach (DataColumn column in sourceTable.Columns)
            {
                res.Columns.Add(column.ColumnName);
            }

            foreach (KeyValuePair<string, LookupField> pair in lookupMultiFields)
            {
                string tp_Id = sourceTable.Rows[0]["tp_Id"].ToString();
                DataRow newRow = sourceTable.Rows[0];
                newRow[pair.Value.Name] = string.Format(";#{0}", newRow[pair.Value.Name]);
                res.ImportRow(newRow);

                for (int i = 1; i < sourceTable.Rows.Count; ++i)
                {
                    if (sourceTable.Rows[i]["tp_Id"].ToString() == tp_Id)
                    {
                        DataRow lastRow = res.Rows[res.Rows.Count - 1];
                        lastRow[pair.Value.Name] = lastRow[pair.Value.Name] +
                                                   string.Format(";#{0}", sourceTable.Rows[i][pair.Value.Name]);
                    }
                    else
                    {
                        DataRow modifiedRow = sourceTable.Rows[i];
                        modifiedRow[pair.Value.Name] = string.Format(";#{0}", modifiedRow[pair.Value.Name]);
                        res.ImportRow(modifiedRow);
                        tp_Id = sourceTable.Rows[i]["tp_Id"].ToString();
                    }
                }

                //break;
            }

            return res;
        }

        private void AddLookupFields(SelectQuery sq)
        {
            foreach (KeyValuePair<string, LookupField> pair in lookupFields)
            {
                string listId = pair.Value.SourceListId;
                FromTerm AllUserDataTale = FromTerm.Table("AllUserData", listId);
                LookupField lf = pair.Value;
                WhereClause wc = new WhereClause();
                wc.Terms.Add(WhereTerm.CreateCompare(
                                 SqlExpression.Field(lf.ColumnnName, sq.FromClause.BaseTable),
                                 SqlExpression.Field("tp_ID", AllUserDataTale),
                                 CompareOperator.Equal));

                wc.Terms.Add(WhereTerm.CreateCompare(
                                 SqlExpression.Field("tp_ListId", AllUserDataTale),
                                 SqlExpression.Constant(SqlConstant.String(listId)),
                                 CompareOperator.Equal));

                sq.FromClause.Join(JoinType.Left, sq.FromClause.BaseTable, AllUserDataTale, wc);

                sq.Columns.Add(new SelectColumn(unfoldedLookupFields[lf.SourceListField],
                                                AllUserDataTale, lf.ColumnnName));
            }
        }

        private SelectQuery AddLookupMultiFields(SelectQuery sq, Guid userListId)
        {
            var multiFieldNames = from pair in lookupMultiFields
                                  select unfoldedLookupFields[pair.Value.SourceListField];

            sq.Columns.Add(new SelectColumn("tp_Id", userDataTable));
            sq.Columns.Add(new SelectColumn("tp_SiteId", userDataTable));

            SelectColumn leafNameColumn = new SelectColumn("LeafName", docsTable, "tp_LeafName");// userDataTable);

            sq.Columns.Add(leafNameColumn);

            leafNameColumn = new SelectColumn("tp_ContentTypeId", userDataTable, "ContentType");

            sq.Columns.Add(leafNameColumn);

            SelectQuery newSq = new SelectQuery();
            foreach (SelectColumn selectColumn in sq.Columns)
            {
                if (!multiFieldNames.Contains(selectColumn.RenderName))
                {
                    newSq.Columns.Add(new SelectColumn(selectColumn.RenderName, FromTerm.Table("InternalTable")));
                }
            }

            FromTerm fromSq = FromTerm.SubQuery(sq, "InternalTable");
            newSq.FromClause.BaseTable = fromSq;

            int i = 0;
            foreach (KeyValuePair<string, LookupField> pair in lookupMultiFields)
            {
                i++;
                FromTerm udjTableAlias = FromTerm.Table(udjTable.RefName, "udj" + i);
                FromTerm allUserDataTableAlias = FromTerm.Table(allUserDataTable.RefName, "aud" + i);

                string multiField = unfoldedLookupFields[pair.Value.SourceListField];
                if (pair.Value.SourceListId.StartsWith(QueryParams.UserMultiSourceListIdStub))
                {
                    newSq.Columns.Add(new SelectColumn(QueryParams.DbUserColumnName, allUserDataTableAlias,
                                                       pair.Value.SourceListField));
                }
                else
                {
                    newSq.Columns.Add(new SelectColumn(multiField, allUserDataTableAlias));
                }

                {
                    WhereClause whereClause = new WhereClause(WhereClauseRelationship.And);
                    whereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("tp_SiteId", FromTerm.Table("InternalTable")),
                                                                  SqlExpression.Field("tp_SiteId", udjTableAlias), CompareOperator.Equal));

                    whereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("tp_LeafName", FromTerm.Table("InternalTable")),
                                                                  SqlExpression.Field("tp_LeafName", udjTableAlias), CompareOperator.Equal));

                    whereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("tp_FieldId", udjTableAlias),
                                                                  SqlExpression.Constant(SqlDataType.String, pair.Value.ID),
                                                                  CompareOperator.Equal));

                    newSq.FromClause.Join(JoinType.Left, newSq.FromClause.BaseTable, udjTableAlias,
                                          whereClause);
                }


                {
                    WhereClause whereClause = new WhereClause(WhereClauseRelationship.And);
                    whereClause.Terms.Add(
                        WhereTerm.CreateCompare(SqlExpression.Field("tp_ListId", allUserDataTableAlias),
                                                SqlExpression.Constant(SqlDataType.String,
                                                                       pair.Value.SourceListId == QueryParams.UserMultiSourceListIdStub
                                                                           ? userListId.ToString("D")
                                                                           : pair.Value.SourceListId),
                                                CompareOperator.Equal));
                    whereClause.Terms.Add(
                        WhereTerm.CreateCompare(SqlExpression.Field("tp_Id", allUserDataTableAlias),
                                                SqlExpression.Field("tp_Id", udjTableAlias), CompareOperator.Equal));

                    newSq.FromClause.Join(JoinType.Left, newSq.FromClause.BaseTable, allUserDataTableAlias,
                                          whereClause);
                }

                //return newSq;
            }

            return newSq;
            //return sq;
        }

        private void QueryLookupFiledsSchema()
        {
            if (lookupFields.Count > 0 || lookupMultiFields.Count > 0)
            {
                SelectQuery sq = new SelectQuery();

                sq.FromClause.BaseTable = AllListsTable;

                //sq.Columns.Add(new SelectColumn("tp_ID", allListsTable));

                sq.Columns.Add(new SelectColumn("tp_Fields", AllListsTable));

                List<string> ids = new List<string>(lookupFields.Count);

                foreach (KeyValuePair<string, LookupField> pair in lookupFields)
                {
                    ids.Add(pair.Value.SourceListId);
                }

                foreach (KeyValuePair<string, LookupField> pair in lookupMultiFields)
                {
                    if (!pair.Value.SourceListId.StartsWith(QueryParams.UserMultiSourceListIdStub))
                    {
                        ids.Add(pair.Value.SourceListId);
                    }
                }

                string idsKey = StringUtil.ArrayToCSV(ids);

                if (cache.ContainsKey(idsKey))
                {
                    unfoldedLookupFields = (Dictionary<string, string>)cache[idsKey];
                }
                else
                {
                    unfoldedLookupFields = new Dictionary<string, string>();

                    //Maybe we have only user_multi lookup fields
                    if (ids.Count > 0)
                    {
                        sq.WherePhrase.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("tp_ID", AllListsTable),
                                                                    SqlConstantCollection.FromList(ids)));

                        using (SqlCommand sc = new SqlCommand((new SqlServerRenderer()).RenderSelect(sq), connection))
                        {
                            using (SqlDataReader sdr = ForceExecuteReader(sc))
                            {
                                ListSchemaParser lsp = new ListSchemaParser();

                                while (sdr.Read())
                                {
                                    string schema = string.Empty;

                                    #region Decode
                                    //MJM This value changed to Byte[] from ntext in v14
                                    object rawValue = sdr.GetValue(0);

                                    if (rawValue.GetType() == typeof(byte[]))
                                    {
                                        schema = Decode(Decompress((byte[])rawValue));
                                    }
                                    else
                                    {
                                        schema = (string)rawValue;
                                    }

                                    #endregion
                                    //string id = sdr.GetString(0);

                                    lsp.ParseDbString(schema);

                                    foreach (KeyValuePair<string, string> pair in lsp.SimpleFields)
                                    {
                                        if (lookupFieldsIndex.ContainsKey(pair.Key)
                                            && !unfoldedLookupFields.ContainsKey(pair.Key))
                                        {
                                            unfoldedLookupFields.Add(pair.Key, pair.Value);
                                        }
                                    }
                                }
                            }
                        }
                    }

                    foreach (KeyValuePair<string, LookupField> pair in lookupMultiFields)
                    {
                        if (pair.Value.SourceListId.StartsWith(QueryParams.UserMultiSourceListIdStub))
                        {
                            unfoldedLookupFields[pair.Value.SourceListField] = pair.Value.SourceListField;
                        }
                    }

                    if (unfoldedLookupFields.Count > 0)
                    {
                        cache[idsKey] = unfoldedLookupFields;
                    }
                }
            }
        }

        private static Dictionary<TKey, TVal> CloneDictonary<TKey, TVal>(Dictionary<TKey, TVal> source)
        {
            Dictionary<TKey, TVal> res = new Dictionary<TKey, TVal>();
            foreach (KeyValuePair<TKey, TVal> pair in source)
            {
                res[pair.Key] = pair.Value;
            }
            return res;
        }

        private void AddUserTypeFields(ref SelectQuery sq,
                                           Dictionary<string, string> fields,
                                           Dictionary<string, string> userFieldsCopy)
        {
            if (userFieldsCopy.Count > 0)
            {
                List<SelectColumn> removeList = new List<SelectColumn>();

                int counter = 0;
                foreach (KeyValuePair<string, string> pair in userFieldsCopy)
                {
                    JoinCondition join1 = new JoinCondition(pair.Value, "tp_ID");
                    JoinCondition join2 = new JoinCondition("tp_SiteId", "tp_SiteID");

                    JoinCondition join3 = new JoinCondition("tp_ID", pair.Value);
                    JoinCondition join4 = new JoinCondition("tp_SiteID", "tp_SiteId");

                    FromTerm newJoinTable = FromTerm.Table("UserInfo", "uf" + counter++.ToString());

                    FromTerm aliasToBaseTable = FromTerm.Table(sq.FromClause.BaseTable.Alias);

                    sq.FromClause.Join(JoinType.Left, aliasToBaseTable, newJoinTable, join1, join2);

                    // This removed the original column reference 
                    // It shouldn't be done here, the columns should not have been added to begin with
                    // It is done here so other 
                    foreach (var column in sq.Columns)
                    {
                        if (column.RenderName == pair.Value)
                        {
                            removeList.Add(column);
                            break;
                        }
                    }

                    sq.Columns.Add(new SelectColumn("tp_Title", newJoinTable, pair.Value));
                }

                foreach (var column in removeList)
                {
                    // remove the original columns (aliases have been added from UserInfo)
                    sq.Columns.Remove(column);
                }
            }
        }

        [Obsolete("Replaced with new AddUserTypeFields")]
        private void AddUserTypeFieldsOld(ref SelectQuery sq,
                                            Dictionary<string, string> fields,
                                            Dictionary<string, string> userFieldsCopy)
        {
            if (userFieldsCopy.Count > 0)
            {
                //runs only once! look at break; this is the only way to take a head from dictionary
                foreach (KeyValuePair<string, string> pair in userFieldsCopy)
                {
                    userFieldsCopy.Remove(pair.Key);
                    //fields.Add(pair.Key, pair.Value);

                    SelectQuery nq = new SelectQuery();
                    nq.FromClause.BaseTable = FromTerm.SubQuery(sq, "uf" + userFieldsCopy.Count);

                    AddColumns(fields, nq);
                    AddColumns(userFieldsCopy, nq);

                    foreach (KeyValuePair<string, LookupField> loookupField in lookupFields)
                    {
                        nq.Columns.Add(new SelectColumn(loookupField.Value.ColumnnName,
                            nq.FromClause.BaseTable));
                    }

                    nq.Columns.Add(new SelectColumn("tp_Title", userInfoTable, pair.Value));
                    nq.FromClause.Join(JoinType.Left, nq.FromClause.BaseTable, userInfoTable,
                                       pair.Value, "tp_ID");

                    fields.Add(pair.Key, pair.Value);
                    sq = nq;
                    AddUserTypeFields(ref sq, fields, userFieldsCopy);
                    break;
                }
            }
        }

        private SqlDataReader ForceExecuteReader(SqlCommand command)
        {
            SqlDataReader res = null;
            string msg = string.Empty;
            for (int i = 0; i < executeAttemtCount; ++i)
            {
                try
                {
                    res = command.ExecuteReader();
                    break;
                }
                catch (Exception ex)
                {
                    msg = ex.Message + ":" + ex.StackTrace + ":" + command.CommandText;
                }
            }

            if (res == null)
                throw new InvalidOperationException("can't execute SQL reader" + "<br/>" + msg);

            return res;
        }

        private DataTable GetResult(SqlCommand extractData, Dictionary<string, string> fields,
                                    DataTable result)
        {
            using (SqlDataReader reader = ForceExecuteReader(extractData))
            {
                if (reader.HasRows)
                {
                    //result = new DataTable("RollupTable");
                    DataTable dt = new DataTable("RollupTable");
                    foreach (string column in fields.Keys)
                    {
                        string sqlColumnName = fields[column];

                        if (options.DataAsStrings)
                        {
                            dt.Columns.Add(new DataColumn(column, typeof(string)));
                            continue;
                        }

                        if (sqlColumnName.StartsWith("int") && !userFields.ContainsKey(column)
                            && !lookupFields.ContainsKey(column))
                            dt.Columns.Add(new DataColumn(column, typeof(int)));
                        else if (sqlColumnName.StartsWith("float"))
                            dt.Columns.Add(new DataColumn(column, typeof(float)));
                        else if (sqlColumnName.StartsWith("datetime"))
                            dt.Columns.Add(new DataColumn(column, typeof(DateTime)));
                        else if (sqlColumnName.StartsWith("bit"))
                            dt.Columns.Add(new DataColumn(column, typeof(bool)));
                        else
                            dt.Columns.Add(new DataColumn(column));
                    }

                    //int i = 0;
                    //while (i < startRow && reader.Read())
                    //{
                    //    ++i;
                    //}

                    //rowLimit += startRow;
                    //bool needLimitRow = rowLimit > 0;

                    while (reader.Read() /*&& ((needLimitRow && i < rowLimit) || !needLimitRow)*/)
                    {
                        List<object> row = new List<object>(dt.Columns.Count);
                        foreach (DataColumn column in dt.Columns)
                        {
                            string sqlColumnName = fields[column.ColumnName];
                            row.Add(reader[sqlColumnName]);
                        }
                        dt.Rows.Add(row.ToArray());
                        //++i;
                    }
                    result = dt;
                }
                else
                {
                    result = new DataTable();
                }
            }
            Postprocess(result);
            return result;
        }

        private void Postprocess(DataTable result)
        {
            FilterByMatching(result);
        }

        private void FilterByMatching(DataTable result)
        {
            foreach (KeyValuePair<string, string> matchField in matches)
            {
                string fieldName = matchField.Key;
                string matchRegex = matchField.Value;

                if (result.Columns.Contains(fieldName))
                {
                    try
                    {
                        Regex match = new Regex(matchRegex);
                        var rowsToDelete = new List<DataRow>();
                        foreach (DataRow row in result.Rows)
                        {
                            if (!match.Match(row[fieldName].ToString()).Success)
                            {
                                //row.Delete();
                                rowsToDelete.Add(row);
                            }
                        }

                        foreach (var row in rowsToDelete)
                        {
                            row.Delete();
                        }
                    }
                    catch (ArgumentException)
                    {
                        //regex parsing error
                    }
                }
            }
        }

        internal Dictionary<string, string> ParseFields(string tp_Fields,
                                                        out Dictionary<string, string> userFields,
                                                        out Dictionary<string, LookupField> lookupFields,
                                                        out Dictionary<string, LookupField> lookupMultiFields)
        {
            Dictionary<string, string> res = new Dictionary<string, string>();

            ListSchemaParser lsp = new ListSchemaParser();
            lsp.ParseDbString(tp_Fields);

            userFields = lsp.UserFields;
            lookupFields = lsp.LookupFields;
            lookupMultiFields = lsp.LookupMultiFields;
            //userMultiFields = lsp.UserMultiFields;
            res = lsp.SimpleFields;

            foreach (string rawField in lsp.RawFields)
            {
                if (builtinFields.ContainsKey(rawField))
                {
                    FieldItem fi = builtinFields[rawField];

                    if (fi.Type == "User")
                    {
                        userFields.Add(rawField, fi.ColumnName);
                    }
                    else
                    {
                        if (!res.ContainsKey(rawField))
                            res.Add(rawField, fi.ColumnName);
                    }
                }
            }
            return res;
        }

        private void ReadBuiltinFields()
        {
            string fieldsFilePath = EnvironmentUtil.GetFieldsFile();
            XPathDocument xDoc = new XPathDocument(fieldsFilePath);
            XPathNavigator xNav = xDoc.CreateNavigator();
            XPathNodeIterator xIter = xNav.Select("//sp:Field[@ColName and @Name and @Type and @ID]",
                                                  SPNamespaceManager);
            string biFieldsKey = "BuiltinFields";
            if (!cache.ContainsKey(biFieldsKey))
            {
                AddBuiltinFields(xIter);
                cache[biFieldsKey] = builtinFields;
            }
            else
            {
                builtinFields = (Dictionary<string, FieldItem>)cache[biFieldsKey];
            }
        }

        private void AddBuiltinFields(XPathNodeIterator xIter)
        {
            foreach (XPathNavigator fieldNode in xIter)
            {
                string name = fieldNode.GetAttribute("Name", string.Empty);
                string columnName = fieldNode.GetAttribute("ColName", string.Empty);
                string id = fieldNode.GetAttribute("ID", string.Empty);
                string type = fieldNode.GetAttribute("Type", string.Empty);

                FieldItem fi = new FieldItem(new Guid(id), name, columnName, type);
                builtinFields.Add(name, fi);
            }
        }

        public void SetOptions(PSSiteDataRollupOptions options, uint startRow, uint rowLimit,
                               PSSiteDataSecuritySettings security, string query, Dictionary<string, string> allFields,
                               Dictionary<string, string> userFields, Dictionary<string, LookupField> lookupFields,
                               Dictionary<string, LookupField> lookupMultiFields, Dictionary<string, string> matches,
                               bool isAdmin, bool isFBAUser)
        {
            this.options = options;

            if (startRow > rowLimit)
                throw new ArgumentException("startRow must be less than rowLimit");

            this.startRow = startRow;
            this.rowLimit = rowLimit;
            this.security = security;
            this.query = query;
            this.allFields = allFields;
            this.userFields = userFields;
            this.lookupFields = lookupFields;
            this.lookupMultiFields = lookupMultiFields;
            this.isAdmin = isAdmin;
            this.isFBAUser = isFBAUser;
            this.matches = matches;

            lookupFieldsIndex.Clear();
            foreach (KeyValuePair<string, LookupField> pair in lookupFields)
            {
                if (!lookupFieldsIndex.ContainsKey(pair.Value.SourceListField))
                    lookupFieldsIndex.Add(pair.Value.SourceListField, null);
            }

            foreach (KeyValuePair<string, LookupField> pair in lookupMultiFields)
            {
                if (!lookupFieldsIndex.ContainsKey(pair.Value.SourceListField))
                    lookupFieldsIndex.Add(pair.Value.SourceListField, null);
            }
        }

        private string InsertToSessionTable(string queryListIds, string queryLists)
        {
            //MJM Check if the table is not already there
            // CREATE TABLE {0} (listId uniqueidentifier, fieldSchema ntext, user_list_id uniqueidentifier);
            string pre = String.Format("IF OBJECT_ID('tempdb..{0}') IS NOT NULL  DROP TABLE	{0};", tempTable);

#if v14
            pre += @"CREATE TABLE " + tempTable + @" (listId uniqueidentifier, fieldSchema varbinary(max), user_list_id uniqueidentifier);";
#else
            pre += @"CREATE TABLE " + tempTable + @" (listId uniqueidentifier, fieldSchema nvarchar(max), user_list_id uniqueidentifier);";      
#endif

            return pre +
                        "INSERT " + tempTable + " (listId, fieldSchema, user_list_id)" +
                                        "\n\r\n\r" + queryListIds + "\n\r\n\r" +
                                           queryLists;
            //0xFFFE +
            //"\n\r\n\r" + queryListIds.Replace("[AllLists].[tp_Fields]", " CAST([AllLists].[tp_Fields] as nvarchar(max)) as fieldSchema ") + "\n\r\n\r" +
            //"\n\r\n\r" + queryListIds.Replace("[AllLists].[tp_Fields]", " 0xFFFE + [AllLists].[tp_Fields] as fieldSchema ") + "\n\r\n\r" +

            //                   @"SELECT tp_Fields
            //FROM " + allListsTable.RefName + Environment.NewLine + 
            //"WHERE tp_ID in (SELECT TOP(1) listId FROM " + tempTable + ")";
        }

        string Decode(byte[] rawContent)
        {
            string listSchema = string.Empty;

            listSchema = Encoding.UTF8.GetString(rawContent);

            return listSchema;
        }

        public static byte[] Decompress(byte[] inArray)
        {
            byte[] buffer = new byte[inArray.Length];

            using (MemoryStream outp = new MemoryStream())
            {
                // Decompress the contents of the input file
                using (MemoryStream ini = new MemoryStream(inArray))
                {
                    //http://msdn.microsoft.com/en-us/library/dd973866(PROT.13).aspx
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();
                    ini.ReadByte();

                    //http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=97064
                    ini.ReadByte();
                    ini.ReadByte();

                    using (DeflateStream inp = new DeflateStream(ini, CompressionMode.Decompress))
                    {
                        int len;

                        while ((len = inp.Read(buffer, 0, buffer.Length)) > 0)
                        {
                            // Write the data block to the decompressed output stream
                            outp.Write(buffer, 0, len);
                        }
                    }
                }
                return outp.ToArray();
            }
        }

        internal string GetAccessibleLists(int userId, WhereTerm[] filters, bool isAdmin, bool isFBAUser)
        {
            SelectQuery sq = GetLists(isAdmin, isFBAUser);

            sq.WherePhrase.Terms.AddRange(filters);

            //var getListsSq = sq.Clone();

            AddUserInfoLIstId(sq);

            //List<DBList> res = new List<DBList>();

            SqlServerRenderer sqlRenderer = new SqlServerRenderer();

            string getListsExpanded = sqlRenderer.RenderSelect(sq);

            //string getListsSqString = sqlRenderer.RenderSelect(getListsSq);

            string getListsSqString = "SELECT * FROM " + tempTable;

            getListsExpanded = InsertToSessionTable(getListsExpanded, getListsSqString);

            SqlCommand getListsCommand = new SqlCommand(getListsExpanded, connection);

            SqlParameter userIdParam = new SqlParameter("UserID", SqlDbType.Int);

            userIdParam.Value = userId;

            getListsCommand.Parameters.Add(userIdParam);

            string listSchema = string.Empty;

            try
            {
                using (SqlDataReader reader = ForceExecuteReader(getListsCommand))
                {
                    while (reader.Read())
                    {
                        if (string.IsNullOrEmpty(listSchema))
                        {
                            #region Decode
                            byte[] rawContent = null;

                            if (reader.GetValue(1).GetType() == typeof(byte[]))
                            {   // v14
                                //MJM This value changed to Byte[] from ntext
                                rawContent = (byte[])reader.GetValue(1);

                                byte[] decompArray = Decompress(rawContent);

                                listSchema = Decode(decompArray);
                            }
                            else
                            {//v12
                                String stringData = (String)reader.GetValue(1);

                                listSchema = reader.GetString(1);
                            }
                            #endregion
                        }

                        Guid listId = reader.GetGuid(0);

                        Guid userInfoListId = reader.GetGuid(2);

                        if (!QueryParams.Instance.ListUserInfos.ContainsKey(userInfoListId))
                        {
                            QueryParams.Instance.ListUserInfos.Add(userInfoListId, new List<Guid>());
                        }

                        QueryParams.Instance.ListUserInfos[userInfoListId].Add(listId);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            getListsCommand.Dispose();

            return listSchema;
        }

        //This method adds field with UserInfo list id, UserInfo list is from source list site collection
        private void AddUserInfoLIstId(SelectQuery sq)
        {
            //sq.WherePhrase.Terms.Clear();
            //AddListIdFromTempTable(sq);

            sq.Columns.Add(new SelectColumn("tp_Fields", sq.FromClause.BaseTable));

            var wcWebs = new WhereClause();
            wcWebs.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("tp_WebId", sq.FromClause.BaseTable),
                                                 SqlExpression.Field("Id", websTable), CompareOperator.Equal));
            sq.FromClause.Join(JoinType.Inner, sq.FromClause.BaseTable, websTable, wcWebs);

            var wcSites = new WhereClause();
            wcSites.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("SiteId", websTable),
                                                      SqlExpression.Field("Id", sitesTable), CompareOperator.Equal));
            sq.FromClause.Join(JoinType.Inner, sq.FromClause.BaseTable, sitesTable, wcSites);

            var wcWebs2 = new WhereClause();
            var webs2Table = FromTerm.Table(websTable.RefName, "w2");
            wcWebs2.Terms.Add(
                WhereTerm.CreateCompare(SqlExpression.Field("Id", webs2Table),
                                        SqlExpression.Field("RootWebId", sitesTable),
                                        CompareOperator.Equal));
            sq.FromClause.Join(JoinType.Inner, sq.FromClause.BaseTable, webs2Table, wcWebs2);

            var wcLists = new WhereClause(WhereClauseRelationship.And);
            var lists2Table = FromTerm.Table(AllListsTable.RefName, "l2");
            wcLists.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("tp_WebId", lists2Table),
                                                      SqlExpression.Field("Id", webs2Table), CompareOperator.Equal));
            wcLists.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("tp_ServerTemplate", lists2Table),
                                                      SqlExpression.Constant(SqlConstant.Number(112)),
                                                      CompareOperator.Equal));
            sq.FromClause.Join(JoinType.Inner, sq.FromClause.BaseTable, lists2Table, wcLists);

            sq.Columns.Add(new SelectColumn("tp_ID", lists2Table, "user_list_id"));

        }

        private void AddListIdFromTempTable(SelectQuery sq)
        {
            sq.WherePhrase.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("tp_ID", sq.FromClause.BaseTable),
                                                        "SELECT listId FROM " + tempTable));
        }

        public void CreateViewsInTmp(string[] contentDBs)
        {
            if (webAppLevel)
            {
                #region Content
                string allListsViewToken =
                            @"SELECT     tp_WebId, tp_ID, tp_Title, tp_Created, tp_Modified, tp_LastDeleted, tp_DeleteCount, tp_LastSecurityChange, tp_Version, tp_Author, tp_BaseType, 
                      tp_FeatureId, tp_ServerTemplate, tp_RootFolder, tp_Template, tp_ImageUrl, tp_ReadSecurity, tp_WriteSecurity, tp_Subscribed, tp_Direction, tp_Flags, 
                      tp_ThumbnailSize, tp_WebImageWidth, tp_WebImageHeight, tp_ItemCount, tp_NextAvailableId, tp_Description, tp_EmailInsertsFolder, 
                      tp_EmailInsertsLastSyncTime, tp_EmailAlias, tp_DeleteTransactionId, tp_ScopeId, tp_HasFGP, tp_HasInternalFGP, tp_EventSinkAssembly, 
                      tp_EventSinkClass, tp_EventSinkData, tp_MaxRowOrdinal, tp_Fields, tp_ContentTypes, tp_AuditFlags, tp_InheritAuditFlags, tp_SendToLocation, 
                      tp_ListDataDirty, tp_CacheParseId, tp_MaxMajorVersionCount, tp_MaxMajorwithMinorVersionCount, tp_DefaultWorkflowId
FROM         {0}.dbo.AllLists";


                string allUserDataViewToken =
                    @"SELECT     tp_ID, tp_ListId, tp_SiteId, tp_RowOrdinal, tp_Version, tp_Author, tp_Editor, tp_Modified, tp_Created, tp_Ordering, tp_ThreadIndex, tp_HasAttachment, 
                      tp_ModerationStatus, tp_IsCurrent, tp_ItemOrder, tp_InstanceID, tp_GUID, tp_CopySource, tp_HasCopyDestinations, tp_AuditFlags, 
                      tp_InheritAuditFlags, tp_Size, tp_WorkflowVersion, tp_WorkflowInstanceID, tp_DirName, tp_LeafName, tp_DeleteTransactionId, tp_ContentType, 
                      tp_ContentTypeId, nvarchar1, nvarchar2, nvarchar3, nvarchar4, nvarchar5, nvarchar6, nvarchar7, nvarchar8, ntext1, ntext2, ntext3, ntext4, 
                      sql_variant1, nvarchar9, nvarchar10, nvarchar11, nvarchar12, nvarchar13, nvarchar14, nvarchar15, nvarchar16, ntext5, ntext6, ntext7, ntext8, 
                      sql_variant2, nvarchar17, nvarchar18, nvarchar19, nvarchar20, nvarchar21, nvarchar22, nvarchar23, nvarchar24, ntext9, ntext10, ntext11, ntext12, 
                      sql_variant3, nvarchar25, nvarchar26, nvarchar27, nvarchar28, nvarchar29, nvarchar30, nvarchar31, nvarchar32, ntext13, ntext14, ntext15, ntext16, 
                      sql_variant4, nvarchar33, nvarchar34, nvarchar35, nvarchar36, nvarchar37, nvarchar38, nvarchar39, nvarchar40, ntext17, ntext18, ntext19, ntext20, 
                      sql_variant5, nvarchar41, nvarchar42, nvarchar43, nvarchar44, nvarchar45, nvarchar46, nvarchar47, nvarchar48, ntext21, ntext22, ntext23, ntext24, 
                      sql_variant6, nvarchar49, nvarchar50, nvarchar51, nvarchar52, nvarchar53, nvarchar54, nvarchar55, nvarchar56, ntext25, ntext26, ntext27, ntext28, 
                      sql_variant7, nvarchar57, nvarchar58, nvarchar59, nvarchar60, nvarchar61, nvarchar62, nvarchar63, nvarchar64, ntext29, ntext30, ntext31, ntext32, 
                      sql_variant8, int1, int2, int3, int4, int5, int6, int7, int8, int9, int10, int11, int12, int13, int14, int15, int16, float1, float2, float3, float4, float5, float6, float7, 
                      float8, float9, float10, float11, float12, datetime1, datetime2, datetime3, datetime4, datetime5, datetime6, datetime7, datetime8, bit1, bit2, bit3, bit4, bit5, 
                      bit6, bit7, bit8, bit9, bit10, bit11, bit12, bit13, bit14, bit15, bit16, uniqueidentifier1, tp_Level, tp_IsCurrentVersion, tp_UIVersion, tp_CalculatedVersion, 
                      tp_UIVersionString, tp_DraftOwnerId, tp_CheckoutUserId
FROM         {0}.dbo.AllUserData";

                string websViewToken =
                    @"SELECT     Id, SiteId, FullUrl, ParentWebId, ProductVersion, TemplateVersion, FirstUniqueAncestorWebId, Author, Title, TimeCreated, CachedNavDirty, CachedNav, 
                      CachedInheritedNav, CachedNavScope, CachedDataVersion, Description, ScopeId, SecurityProvider, MetaInfo, MetaInfoVersion, LastMetadataChange, 
                      NavStructNextEid, NavParentWebId, NextWebGroupId, DefTheme, AlternateCSSUrl, CustomizedCss, CustomJSUrl, AlternateHeaderUrl, 
                      DailyUsageData, DailyUsageDataVersion, MonthlyUsageData, MonthlyUsageDataVersion, DayLastAccessed, WebTemplate, Language, Locale, 
                      TimeZone, Time24, CalendarType, AdjustHijriDays, MeetingCount, ProvisionConfig, Flags, Collation, RequestAccessEmail, MasterUrl, 
                      CustomMasterUrl, SiteLogoUrl, SiteLogoDescription, AuditFlags, InheritAuditFlags, Ancestry, AltCalendarType, CalendarViewOptions, WorkDays, 
                      WorkDayStartHour, WorkDayEndHour, EmailEnabled
FROM         {0}.dbo.Webs";

                string docsViewToken =
                    @"SELECT     Id, SiteId, DirName, LeafName, WebId, ListId, DoclibRowId, Type, Size, MetaInfoSize, Version, UIVersion, Dirty, ListDataDirty, CacheParseId, DocFlags,
                       ThicketFlag, CharSet, ProgId, TimeCreated, TimeLastModified, NextToLastTimeModified, MetaInfoTimeLastModified, TimeLastWritten, 
                      DeleteTransactionId, SetupPathVersion, SetupPath, SetupPathUser, CheckoutUserId, CheckoutDate, CheckoutExpires, VersionCreatedSinceSTCheckout, 
                      LTCheckoutUserId, VirusVendorID, VirusStatus, VirusInfo, MetaInfo, UnVersionedMetaInfo, UnVersionedMetaInfoSize, UnVersionedMetaInfoVersion, 
                      WelcomePageUrl, WelcomePageParameters, IsCurrentVersion, [Level], CheckinComment, AuditFlags, InheritAuditFlags, DraftOwnerId, UIVersionString, 
                      ParentId, HasStream, ScopeId, BuildDependencySet, ParentVersion, ParentVersionString, TransformerId, ParentLeafName, IsCheckoutToLocal, 
                      CtoOffset, Extension, ExtensionForFile, ItemChildCount, FolderChildCount
FROM         {0}.dbo.Docs";

                string userDataViewToken =
                    @"SELECT     tp_ID, tp_ListId, tp_SiteId, tp_RowOrdinal, tp_Version, tp_Author, tp_Editor, tp_Modified, tp_Created, tp_Ordering, tp_ThreadIndex, tp_HasAttachment, 
                      tp_ModerationStatus, tp_IsCurrent, tp_ItemOrder, tp_InstanceID, tp_GUID, tp_CopySource, tp_HasCopyDestinations, tp_AuditFlags, 
                      tp_InheritAuditFlags, tp_Size, tp_WorkflowVersion, tp_WorkflowInstanceID, tp_DirName, tp_LeafName, tp_DeleteTransactionId, tp_ContentType, 
                      tp_ContentTypeId, nvarchar1, nvarchar2, nvarchar3, nvarchar4, nvarchar5, nvarchar6, nvarchar7, nvarchar8, ntext1, ntext2, ntext3, ntext4, 
                      sql_variant1, nvarchar9, nvarchar10, nvarchar11, nvarchar12, nvarchar13, nvarchar14, nvarchar15, nvarchar16, ntext5, ntext6, ntext7, ntext8, 
                      sql_variant2, nvarchar17, nvarchar18, nvarchar19, nvarchar20, nvarchar21, nvarchar22, nvarchar23, nvarchar24, ntext9, ntext10, ntext11, ntext12, 
                      sql_variant3, nvarchar25, nvarchar26, nvarchar27, nvarchar28, nvarchar29, nvarchar30, nvarchar31, nvarchar32, ntext13, ntext14, ntext15, ntext16, 
                      sql_variant4, nvarchar33, nvarchar34, nvarchar35, nvarchar36, nvarchar37, nvarchar38, nvarchar39, nvarchar40, ntext17, ntext18, ntext19, ntext20, 
                      sql_variant5, nvarchar41, nvarchar42, nvarchar43, nvarchar44, nvarchar45, nvarchar46, nvarchar47, nvarchar48, ntext21, ntext22, ntext23, ntext24, 
                      sql_variant6, nvarchar49, nvarchar50, nvarchar51, nvarchar52, nvarchar53, nvarchar54, nvarchar55, nvarchar56, ntext25, ntext26, ntext27, ntext28, 
                      sql_variant7, nvarchar57, nvarchar58, nvarchar59, nvarchar60, nvarchar61, nvarchar62, nvarchar63, nvarchar64, ntext29, ntext30, ntext31, ntext32, 
                      sql_variant8, int1, int2, int3, int4, int5, int6, int7, int8, int9, int10, int11, int12, int13, int14, int15, int16, float1, float2, float3, float4, float5, float6, float7, 
                      float8, float9, float10, float11, float12, datetime1, datetime2, datetime3, datetime4, datetime5, datetime6, datetime7, datetime8, bit1, bit2, bit3, bit4, bit5, 
                      bit6, bit7, bit8, bit9, bit10, bit11, bit12, bit13, bit14, bit15, bit16, uniqueidentifier1, tp_Level, tp_IsCurrentVersion, tp_UIVersion, tp_CalculatedVersion, 
                      tp_UIVersionString, tp_DraftOwnerId, tp_CheckoutUserId
FROM         {0}.dbo.UserData";

                string userInfoViewToken =
                    @"SELECT     tp_SiteID, tp_ID, tp_DomainGroup, tp_GUID, tp_SystemID, tp_Deleted, tp_SiteAdmin, tp_IsActive, tp_Login, tp_Title, tp_Email, tp_Notes, tp_Token, 
                      tp_ExternalToken, tp_ExternalTokenLastUpdated, tp_Locale, tp_CalendarType, tp_AdjustHijriDays, tp_TimeZone, tp_Time24, tp_AltCalendarType, 
                      tp_CalendarViewOptions, tp_WorkDays, tp_WorkDayStartHour, tp_WorkDayEndHour
FROM         {0}.dbo.UserInfo";

                string userDataJunctionsViewToken =
                    @"SELECT     tp_SiteId, tp_DeleteTransactionId, tp_IsCurrentVersion, tp_DirName, tp_LeafName, tp_FieldId, tp_CalculatedVersion, tp_Level, tp_UIVersion, tp_Id, 
                      tp_Ordinal
FROM         {0}.dbo.UserDataJunctions";

                string roleAssignmentViewToken =
                    @"SELECT     SiteId, ScopeId, RoleId, PrincipalId
FROM         {0}.dbo.RoleAssignment";

                string groupMembershipViewToken =
                    @"SELECT     SiteId, GroupId, MemberId
FROM         {0}.dbo.GroupMembership";

                string dropViewToken = @"IF OBJECT_ID(N'[dbo].[{0}]') IS NOT NULL
DROP VIEW [dbo].[{0}]";
                #endregion

                Dictionary<string, string> viewTokens = new Dictionary<string, string>
                                                            {
                                                                {"AllListsView", allListsViewToken},
                                                                {"AllUserDataView", allUserDataViewToken},
                                                                {"WebsView", websViewToken},
                                                                {"DocsView", docsViewToken},
                                                                {"UserDataView", userDataViewToken},
                                                                {"UserInfoView", userInfoViewToken},
                                                                {"UserDataJunctionsView", userDataJunctionsViewToken},
                                                                {"RoleAssignmentView", roleAssignmentViewToken},
                                                                {"GroupMembershipView", groupMembershipViewToken}
                                                            };

                foreach (KeyValuePair<string, string> viewToken in viewTokens)
                {
                    string viewName = viewToken.Key;

                    StringBuilder selectFromManyDBsQuery = new StringBuilder();

                    for (int i = 0; i < contentDBs.Length; i++)
                    {
                        string contentDB = contentDBs[i];
                        selectFromManyDBsQuery.AppendFormat(viewToken.Value, contentDB);
                        selectFromManyDBsQuery.AppendLine(string.Format(" AS {0}_{1}", viewName, i));

                        if (i < contentDBs.Length - 1)
                        {
                            selectFromManyDBsQuery.AppendLine("UNION ALL");
                        }
                    }

                    StringBuilder dropViewQuery = new StringBuilder();

                    dropViewQuery.AppendLine(@"USE [tempdb];");
                    dropViewQuery.AppendFormat(dropViewToken, viewName);

                    SqlCommand dropViews = new SqlCommand(dropViewQuery.ToString(), connection);

                    dropViews.ExecuteNonQuery();

                    string createViewQuery = string.Format(@"CREATE VIEW [dbo].[{0}]
AS {1}",
                                                           viewName, selectFromManyDBsQuery);


                    SqlCommand createViews = new SqlCommand("USE [tempdb];", connection);

                    SqlTransaction transaction = connection.BeginTransaction();

                    createViews.Transaction = transaction;

                    createViews.ExecuteNonQuery();

                    createViews.CommandText = createViewQuery;
                    createViews.ExecuteNonQuery();

                    transaction.Commit();
                }
            }
        }
    }

    public class DBList
    {
        private Guid id;
        private string title;
        private string fields;
        private int serverTemplate;

        public Guid Id
        {
            [DebuggerStepThrough]
            get { return id; }

            [DebuggerStepThrough]
            set { id = value; }
        }

        public string Title
        {
            [DebuggerStepThrough]
            get { return title; }

            [DebuggerStepThrough]
            set { title = value; }
        }

        public string Fields
        {
            [DebuggerStepThrough]
            get { return fields; }

            [DebuggerStepThrough]
            set { fields = value; }
        }

        public int ServerTemplate
        {
            [DebuggerStepThrough]
            get { return serverTemplate; }

            [DebuggerStepThrough]
            set { serverTemplate = value; }
        }

        public DBList(string id, string title, string fields, int serverTemplate)
        {
            if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(title) || string.IsNullOrEmpty(fields))
                throw new ArgumentNullException();

            Id = new Guid(id);
            Title = title;
            Fields = fields;
            ServerTemplate = serverTemplate;
        }
    }

    public class GenericDAL : DAL
    {
        public WhereTerm GetScopeConstrait(RollUpQueryWebScope scope, Guid spObjID, string webUrl)
        {
            WhereTerm scopeConstrait;
            switch (scope)
            {
                case RollUpQueryWebScope.Web:
                    scopeConstrait = CreateWebScope(spObjID);
                    break;
                case RollUpQueryWebScope.Recursive:
                    {
                        if (webUrl != null)
                        {
                            scopeConstrait = CreateRecursiveScope(webUrl);
                        }
                        else
                        {
                            throw new ArgumentNullException("webUrl", "web url can not be null when scope is recursive");
                        }
                        break;
                    }
                case RollUpQueryWebScope.SiteCollection:
                    scopeConstrait = CreateSiteScope(spObjID);
                    break;
                default:
                    throw new ArgumentOutOfRangeException("scope");
            }
            return scopeConstrait;
        }
    }
}
